//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

package com.aidong.dao.wills;

import com.aidong.dao.wills.BaseDao;
import com.aidong.model.wills.DayDprModel;
import com.aidong.model.wills.ShopDpr;
import com.aidong.model.wills.ShopDprPK;
import com.aidong.util.DateUtil;
import java.util.Date;
import java.util.List;
import org.springframework.stereotype.Repository;

@Repository
public class ShopDprDao extends BaseDao<ShopDpr, ShopDprPK> {
    public ShopDprDao() {
    }

    public List<DayDprModel> getDprDataOfList(Date dateFrom, Date dateTo) {
        String dateFromYMD = DateUtil.formatDate(dateFrom, DateUtil.DATE_PATTERN_YYYYMMDD);
        String strPeriod = dateFromYMD.substring(0, 7);
        String sqlStrList = "Select k.ShopCode,y.ChName ShopName, l.deptmanager deptmanager,k.nmutarget, k.nmuSys , CASE WHEN k.nmutarget = 0 THEN \'0\' ELSE CONVERT(varchar,CONVERT(numeric(18, 2),k.nmuSys/k.nmutarget * 100)) + \'%\' END nmutargetSys ,k.pitarget, k.ptSys, CASE WHEN k.pitarget = 0 THEN \'0\' ELSE CONVERT(varchar,CONVERT(numeric(18, 2),k.ptSys/k.pitarget * 100)) + \'%\' END pitargetSys ,k.othersaletarget,k.newmembertarget, CASE WHEN k.newmembertarget = 0 THEN \'0\' ELSE CONVERT(varchar,CONVERT(numeric(18, 2),k.nmunewSys/k.newmembertarget * 100)) + \'%\' END newmembertargetSys ,k.nmunewSys,k.guesttarget,k.trafficSys,k.ptupgradeSys,k.ptposSys,k.guestSys,k.ptposunitSys, CASE WHEN k.nmunewSys = 0 THEN \'0\' ELSE CONVERT(varchar,CONVERT(numeric(18, 2),k.ptnewSys/k.nmunewSys * 100)) + \'%\' END posSys ,k.ptnewSys, CASE WHEN k.nmunewSys = 0 THEN \'0\' ELSE CONVERT(varchar,CONVERT(numeric(18, 2),(k.ptposunitSys+k.ptNewQty)/k.nmunewSys * 100)) + \'%\' END npSys , CASE WHEN k.ptSys = 0 THEN \'0\' ELSE CONVERT(varchar,CONVERT(numeric(18, 2),(k.ptupgradeSys)/k.ptSys * 100)) + \'%\' END remewalMix ,k.ptsesssvcSys,k.pibeautytarget,k.pibeautytargetSys, CASE WHEN k.pibeautytarget = 0 THEN \'0\' ELSE CONVERT(varchar,CONVERT(numeric(18, 2),k.pibeautytargetSys/k.pibeautytarget * 100)) + \'%\' END pibeautytargetSysP ,k.productsaletarget,k.productsaletargetSys,k.returnBox,k.boxrent,k.stopedCard,k.changeCard,k.changeshop,k.buCard,k.refundcard,k.ptdelay, CASE WHEN k.productsaletarget = 0 THEN \'0\' ELSE CONVERT(varchar,CONVERT(numeric(18, 2),k.productsaletargetSys/k.productsaletarget * 100)) + \'%\' END productsaletargetSysP ,k.gncsaletarget,k.gncsaletargetSys, CASE WHEN k.gncsaletarget = 0 THEN \'0\' ELSE CONVERT(varchar,CONVERT(numeric(18, 2),k.gncsaletargetSys/k.gncsaletarget * 100)) + \'%\' END gncsaletargetSysP ,k.ptNewAmount,k.ptNewQty,k.ptUpgradeQty,k.nmuDeposit,k.ptDeposit,k.otherDeposit,k.characterCourseAmount,k.characterCourseQty,k.gcAmount,k.giftCard,k.aidong,k.cosmeticTarget,k.cosmeticTargetSys, CASE WHEN k.cosmeticTarget = 0 THEN \'0\' ELSE CONVERT(varchar,CONVERT(numeric(18, 2),k.cosmeticTargetSys/k.cosmeticTarget * 100)) + \'%\' END cosmeticTargetSysP  From (" + this.getDprDataSql(dateFrom, dateTo) + ") k inner join DicShopCode y on k.shopcode = y.Code " + " inner join shopdpr l on k.shopcode = l.shopcode and l.period=\'" + strPeriod + "\'" + " order by y.Sort";
        return this.queryListBySql(DayDprModel.class, sqlStrList, new Object[0]);
    }

    public List<DayDprModel> getDprDataOfTotal(Date dateFrom, Date dateTo) {
        String sqlStrTotal = "Select z.AreaType, z.Code shopcode, z.ChName ShopName, CASE WHEN z.AreaType=\'1\' THEN Sum(nmutarget) ELSE 0 END nmutarget , CASE WHEN z.AreaType=\'1\' THEN Sum(nmuSys + nmuDeposit) ELSE 0 END nmuSys , CASE WHEN z.AreaType=\'2\' THEN Sum(pitarget) ELSE 0 END pitarget , CASE WHEN z.AreaType=\'2\' THEN Sum(ptSys + gncsaletargetSys) ELSE 0 END ptSys  From (" + this.getDprDataSql(dateFrom, dateTo) + ") k inner join DicAdmAreaList y on k.shopcode = y.ShopCode " + " inner join DicAdmAreaCode z on z.Code = y.AreaCode group by z.AreaType, z.Code,z.ChName";
        return this.queryListBySql(DayDprModel.class, sqlStrTotal, new Object[0]);
    }

    public String getDprDataSql(Date dateFrom, Date dateTo) {
        String dateFromYMD = DateUtil.formatDate(dateFrom, DateUtil.DATE_PATTERN_YYYYMMDD) + " 02:00:00";
        String dateToYMD = DateUtil.formatDate(DateUtil.addDays(dateTo, 1), DateUtil.DATE_PATTERN_YYYYMMDD) + " 01:59:59";
        String strPeriod = dateFromYMD.substring(0, 7);
        String strPeriodFrom = dateFromYMD.substring(0, 7) + "-01";
        String strPeriodEnd = DateUtil.formatDate(DateUtil.addDays(DateUtil.addMonths(strPeriodFrom, 1), -1), DateUtil.DATE_PATTERN_YYYYMMDD);
        Integer intDays = Integer.valueOf(DateUtil.nDaysBetweenTwoDate(dateFrom, dateTo) + 1);
        Integer intTotalDays = Integer.valueOf(DateUtil.nDaysBetweenTwoDate(strPeriodFrom, strPeriodEnd) + 1);
        String sqlStr = "Select ShopCode,Sum(nmutarget) as nmutarget,sum(nmuSys) as nmuSys ,sum(pitarget) as pitarget,sum(ptSys) as ptSys,sum(othersaletarget) as othersaletarget,sum(returnBox) as returnBox,sum(boxrent) as boxrent,sum(stopedCard) as stopedCard,sum(buCard) as buCard,sum(changeCard) as changeCard,sum(changeshop) as changeshop,sum(refundcard) as refundcard,sum(newmembertarget) newmembertarget,sum(nmunewSys) as nmunewSys,sum(guesttarget) as guesttarget,sum(trafficSys) as trafficSys,sum(ptupgradeSys) as ptupgradeSys,sum(ptposSys) as ptposSys,sum(guestSys) as guestSys,sum(ptposunitSys) as ptposunitSys,sum(ptnewSys) as ptnewSys,sum(ptsesssvcSys) as ptsesssvcSys,sum(pibeautytarget) as  pibeautytarget,sum(pibeautytargetSys) as  pibeautytargetSys,sum(productsaletarget) as  productsaletarget,sum(productsaletargetSys) as  productsaletargetSys,sum(ptdelay) as  ptdelay,sum(gncsaletarget) as  gncsaletarget,sum(gncsaletargetSys) as  gncsaletargetSys,sum(ptUpgradeQty) as  ptUpgradeQty,sum(ptNewQty) as  ptNewQty,sum(ptNewAmount) as  ptNewAmount,sum(ptDeposit) as  ptDeposit,sum(nmuDeposit) as  nmuDeposit,sum(otherDeposit) as  otherDeposit,sum(characterCourseAmount) as  characterCourseAmount,sum(characterCourseQty) as  characterCourseQty,sum(gcAmount) as gcAmount,sum(giftCard) as giftCard,sum(aidong) as aidong,sum(cosmeticTarget) as  cosmeticTarget,sum(cosmeticTargetSys) as  cosmeticTargetSys From (";
        String sqlWhere = " and period=\'" + strPeriod + "\'";
        sqlStr = sqlStr + " select shopcode" + ",sum(cardsaletarget*" + intDays + "/" + intTotalDays + ") as nmutarget" + ",0 as  nmuSys " + ",sum(pitarget*" + intDays + "/" + intTotalDays + ") as  pitarget" + ",0 as  ptSys" + ",sum(othersaletarget*" + intDays + "/" + intTotalDays + ") as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",sum(guesttarget*" + intDays + "/" + intTotalDays + ") as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",sum(pibeautytarget*" + intDays + "/" + intTotalDays + ") as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",sum(productsaletarget*" + intDays + "/" + intTotalDays + ") as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",sum(gncsaletarget*" + intDays + "/" + intTotalDays + ") as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong" + ",sum(isnull(cosmeticTarget,0)*" + intDays + "/" + intTotalDays + ") as cosmeticTarget, 0 as cosmeticTargetSys" + " from shopdpr where 1=1 " + sqlWhere + " group by shopcode";
        sqlWhere = "";
        sqlWhere = " and saledate>=\'" + dateFromYMD + "\'";
        sqlWhere = sqlWhere + " and saledate<=\'" + dateToYMD + "\'";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",sum(PayAmount) as nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From MemberContract where 1=1 " + sqlWhere + " group by shopcode";
        sqlWhere = "";
        sqlWhere = " and saledate>=\'" + dateFromYMD + "\'";
        sqlWhere = sqlWhere + " and saledate<=\'" + dateToYMD + "\'";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",sum(PayAmount) as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From PtContract pc inner join  PtItemCode pi on pc.PtCode = pi.Code " + "where saletype in (\'1\', \'5\') " + sqlWhere + " group by shopcode";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",sum(PayAmount) as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From PtContract pc inner join  PtItemCode pi on pc.PtCode = pi.Code and pi.ItemType=\'03\' " + "where saletype in (\'1\', \'5\') " + sqlWhere + " group by shopcode";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",sum(PayAmount) as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From PtContract pc inner join (select Code,ChName from PtItemCode where ChName like \'%特色课程%\')  pi on pc.PtCode = pi.Code " + "where saletype in (\'1\', \'5\') " + sqlWhere + " group by shopcode";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",count(ContractCode) as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From PtContract pc inner join (select Code,ChName from PtItemCode where ChName like \'%特色课程%\')  pi on pc.PtCode = pi.Code " + "where saletype=\'1\' " + sqlWhere + " group by shopcode";
        sqlWhere = "";
        sqlWhere = " and ReturnDate>=\'" + dateFromYMD + "\'";
        sqlWhere = sqlWhere + " and ReturnDate<=\'" + dateToYMD + "\'";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",sum(AddAmount+ReturnDeposit*-1) as returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From BoxRent where 1=1 " + sqlWhere + " group by shopcode";
        sqlWhere = "";
        sqlWhere = " and saledate>=\'" + dateFromYMD + "\'";
        sqlWhere = sqlWhere + " and saledate<=\'" + dateToYMD + "\'";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",sum(PayAmount) as boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From BoxRent where 1=1 " + sqlWhere + " group by ShopCode";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",sum(PayAmount) as stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From MemberStopedCard where 1=1 " + sqlWhere + " group by shopcode";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",sum(PayAmount) as buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as   pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From MemberChangeCard where 1=1 " + sqlWhere + " group by ShopCode";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",sum(PayAmount) as changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From MemberContract where saletype=\'4\' " + sqlWhere + " group by shopcode";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",sum(PayAmount) as changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From MemberChangeShop where 1=1 " + sqlWhere + " group by shopcode";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",sum(refundamount * -1) as refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From MemberRefundCard where 1=1 " + sqlWhere + " group by shopcode";
        sqlStr = sqlStr + " union all select a.shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",sum(a.Price * a.Quantity) as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From PssProductSaleList a " + " inner join PssProductSale b on a.ProductSaleId =b.AutoId where ProductCode not in (select ProductCode from PssProductCode where ProductType in (\'10300\',\'10900\')) " + " and ProductCode <> \'10604\' " + sqlWhere + " group by a.shopcode";
        sqlStr = sqlStr + " union all select a.shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",sum(a.Price * a.Quantity) as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From PssProductSaleList a " + " inner join PssProductSale b on a.ProductSaleId =b.AutoId where ProductCode not in (select ProductCode from PssProductCode where ProductType in (\'10300\',\'10900\')) " + " and ProductCode = \'10604\' " + sqlWhere + " group by a.shopcode";
        sqlStr = sqlStr + " union all select a.shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",sum(a.Price * a.Quantity) as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From PssProductSaleList a " + " inner join PssProductSale b on a.ProductSaleId =b.AutoId where ProductCode in (select ProductCode from PssProductCode where ProductType = \'10300\') " + sqlWhere + " group by a.shopcode";
        sqlStr = sqlStr + " union all select a.shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, sum(a.Price * a.Quantity) cosmeticTargetSys" + " From PssProductSaleList a " + " inner join PssProductSale b on a.ProductSaleId =b.AutoId where ProductCode in (select ProductCode from PssProductCode where ProductType = \'10900\') " + sqlWhere + " group by a.shopcode";
        sqlWhere = "";
        sqlWhere = " and period=\'" + strPeriod + "\'";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",sum(newmembertarget*" + intDays + "/" + intTotalDays + ") as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " from shopdpr where 1=1 " + sqlWhere + " group by shopcode";
        sqlWhere = "";
        sqlWhere = " and saledate>=\'" + dateFromYMD + "\'";
        sqlWhere = sqlWhere + " and saledate<=\'" + dateToYMD + "\'";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",count(*) as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From MemberContract where saletype=\'1\' " + sqlWhere + " group by shopcode";
        sqlWhere = "";
        sqlWhere = " and intime>=\'" + dateFromYMD + "\'";
        sqlWhere = sqlWhere + " and intime<=\'" + dateToYMD + "\'";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",count(*) as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From MemberUse where 1=1 " + sqlWhere + " group by shopcode";
        sqlWhere = "";
        sqlWhere = " and saledate>=\'" + dateFromYMD + "\'";
        sqlWhere = sqlWhere + " and saledate<=\'" + dateToYMD + "\'";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",sum(PayAmount) as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From MemberEarnest where EarnestMerge=0 and EarnestType=1 " + sqlWhere + " group by shopcode";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",sum(PayAmount) as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From MemberEarnest where EarnestMerge=0 and EarnestType=\'2\' " + sqlWhere + " group by shopcode";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",sum(PayAmount) as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From MemberEarnest where EarnestMerge=0 and EarnestType not in (\'1\', \'2\') " + sqlWhere + " group by shopcode";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",count(ContractCode) as  ptNewQty" + ",sum(FactPrice) as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From PtContract where saletype=1 and SaleClassify=2 " + sqlWhere + " group by shopcode";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",sum(FactPrice) as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",count(ContractCode) as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From PtContract where saletype=1 and SaleClassify=1 " + sqlWhere + " group by shopcode";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",sum(FactPrice) as  ptposSys" + ",0 as  guestSys" + ",count(*) as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From PtContract where saletype=1 and SaleClassify=3" + sqlWhere + " group by shopcode";
        sqlWhere = "";
        sqlWhere = " and cleartime>=\'" + dateFromYMD + "\'";
        sqlWhere = sqlWhere + " and cleartime<=\'" + dateToYMD + "\'";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",count(*) as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From PtBespeak where cleartime is not null " + sqlWhere + " group by shopcode";
        sqlWhere = " and cleartime>=\'" + dateFromYMD + "\'";
        sqlWhere = sqlWhere + " and cleartime<=\'" + dateToYMD + "\'";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,sum(factPrice) gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From GcBespeak where status=1 " + sqlWhere + " group by shopcode";
        sqlWhere = " and SaleDate>=\'" + dateFromYMD + "\'";
        sqlWhere = sqlWhere + " and SaleDate<=\'" + dateToYMD + "\'";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,sum(Amount) giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From PayList where PayType=\'S014\' " + sqlWhere + " group by shopcode";
        sqlWhere = " and SaleDate>=\'" + dateFromYMD + "\'";
        sqlWhere = sqlWhere + " and SaleDate<=\'" + dateToYMD + "\'";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",0 as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",0 as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,0 gcAmount,0 giftCard,sum(Amount) aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From PayList where PayType=\'S008\' " + sqlWhere + " group by shopcode";
        sqlStr = sqlStr + " union all select shopcode" + ",0 as nmutarget" + ",0 as  nmuSys " + ",0 as  pitarget" + ",sum(case when saletype=\'02\' then -amount else 0 end) as  ptSys" + ",0 as  othersaletarget" + ",0 as  returnBox" + ",0 as  boxrent" + ",0 as  stopedCard" + ",0 as  buCard" + ",0 as  changeCard" + ",0 as  changeshop" + ",0 as  refundcard" + ",0 as  newmembertarget" + ",0 as  nmunewSys" + ",0 as  guesttarget" + ",0 as  trafficSys" + ",0 as  ptupgradeSys" + ",0 as  ptposSys" + ",0 as  guestSys" + ",0 as  ptposunitSys" + ",0 as  ptnewSys" + ",0 as  ptsesssvcSys" + ",0 as  pibeautytarget" + ",0 as  pibeautytargetSys" + ",0 as  productsaletarget" + ",sum(case when saletype=\'03\' then -amount else 0 end) as  productsaletargetSys" + ",0 as  ptdelay" + ",0 as  gncsaletarget" + ",0 as  gncsaletargetSys" + ",0 as  ptUpgradeQty" + ",0 as  ptNewQty" + ",0 as  ptNewAmount" + ",0 as  ptDeposit" + ",0 as  nmuDeposit" + ",0 as  otherDeposit" + ",0 as characterCourseAmount" + ",0 as characterCourseQty,sum(case when saletype=\'11\' then -amount else 0 end) gcAmount,0 giftCard,0 aidong,0 cosmeticTarget, 0 cosmeticTargetSys" + " From PayList where PayType in (\'S014\',\'S008\') " + sqlWhere + " group by shopcode";
        sqlStr = sqlStr + ") x  group by x.shopcode";
        return sqlStr;
    }
}
